#coding=utf-8
import os
import re
import warnings
import numpy as np
import collections
from DataBase.DatabaseConsts import *
import pandas._libs.lib as lib
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from pandas import compat
import pymysql


class MySQLConnect:

    engine = None
    session = None
    def __init__(self, host=MySQLServerIP_199, port=3306, user="root", passwd="root", charset='utf8', db=LiduoLikongDB):
        # 初始化数据库连接，使用pymysql模块
        # MySQL的用户：root, 密码:147369, 端口：3306,数据库：mydb
        connect = "mysql+pymysql://{}:{}@{}:{}/{}?charset={}".format(user, passwd, host, port, db, charset)
        self.engine = create_engine(connect)

    def get_session(self):
        if self.session is None:
            DBSession = sessionmaker(bind=self.engine)
            self.session = DBSession()
        return self.session

    def __del__(self):
        if self.session is not None:
            self.session.close_all()

        # if self.engine is not None:
        #     self.engine.dispose()

    def get_connect(self):
        return self.engine

    @staticmethod
    def get_mysql_db_uri(host=MySQLServerIP_199, db=LiduoLikongDB):
        db_uri = "mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8". \
            format(username="root", password="root", host=host, port="3306", db=db)
        return db_uri


string_types = str,
integer_types = int,
class_types = type,
text_type = str
binary_type = bytes
Set = collections.abc.Set
_SAFE_NAMES_WARNING = ("The spaces in these column names will not be changed. "
                       "In pandas versions < 0.14, spaces were converted to "
                       "underscores.")


def _get_unicode_name(name):
    try:
        uname = text_type(name).encode("utf-8", "strict").decode("utf-8")
    except UnicodeError:
        raise ValueError(
            "Cannot convert identifier to UTF-8: '{name}'".format(name=name))
    return uname


def _get_valid_sqlite_name(name):
    # See http://stackoverflow.com/questions/6514274/how-do-you-escape-strings\
    # -for-sqlite-table-column-names-in-python
    # Ensure the string can be encoded as UTF-8.
    # Ensure the string does not include any NUL characters.
    # Replace all " with "".
    # Wrap the entire thing in double quotes.

    uname = _get_unicode_name(name)
    if not len(uname):
        raise ValueError("Empty table or column name specified")

    nul_index = uname.find("\x00")
    if nul_index >= 0:
        raise ValueError('SQLite identifier cannot contain NULs')
    return uname.replace('"', '""')


string_and_binary_types = string_types + (binary_type,)


def is_list_like(obj, allow_sets=True):
    return (isinstance(obj, compat.Iterable)
            # we do not count strings/unicode/bytes as list-like
            and not isinstance(obj, string_and_binary_types)

            # exclude zero-dimensional numpy arrays, effectively scalars
            and not (isinstance(obj, np.ndarray) and obj.ndim == 0)

            # exclude sets if allow_sets is False
            and not (allow_sets is False and isinstance(obj, Set)))


class MySQLObject:
    name = ""
    pd_sql = None
    keys = None
    frame = None
    index = None
    if_exists = None
    index_label = None
    dtype = None
    schema = None

    def __init__(self):
        pass

    def init_table(self, con=None, table='', frame=None, keys=None, if_exists='append', index=None,
                    index_label=None, schema=None, dtype=None):
        self.name = table
        self.pd_sql = con
        self.keys = keys
        self.frame = frame
        self.index = index
        self.if_exists = if_exists
        self.index_label = index_label
        self.dtype = dtype
        self.schema = table

        query = ("SELECT t.table_name from information_schema.TABLES t "
                 "where t.TABLE_SCHEMA =\'{}\' and t.TABLE_NAME =\'{}\';").format(schema, table)

        var = con.execute(query)
        if var.rowcount > 0:
            return

        sql_stmts = self._create_table_setup()
        for item in sql_stmts:
            con.execute(item)

    def save_data_bat(self, con=None, schema=None, table='', df=None):
        try:
            if (table is None) or (len(schema) == 0) or (len(table) == 0) or (con is None):
                return

            order = ['code', 'name', 'title', 'url', 'date', 'type', 'goodorbad']
            df = df[order]

            stmt_header = "INSERT IGNORE INTO {}.{} (".format(schema, table)
            for i in range(len(df.columns)):
                if i != 0:
                    stmt_header += ","
                stmt_header += text_type(df.columns[i])
            stmt_header += ") VALUES "

            index = 0
            stmt_sql = stmt_header
            need_last_call = False
            while index < len(df.values):
                item = df.loc[index]
                stmt_item = "(\"{}\",\"{}\",\"{}\",\"{}\",\"{}\",\"{}\",{})".format(text_type(item['code']),
                                                                                    text_type(item['name']),
                                                                                    text_type(item['title']),
                                                                                    text_type(item['url']),
                                                                                    text_type(item['date']),
                                                                                    text_type(item['type']),
                                                                                    item['goodorbad'])
                stmt_sql += pymysql.escape_string(stmt_item)
                if len(stmt_sql) > 20000:
                    stmt_sql += ";"
                    print(stmt_sql)
                    con.execute(stmt_sql)
                    stmt_sql = stmt_header
                    need_last_call = False
                else:
                    if index == len(df.values) - 1:
                        stmt_sql += ";"
                    else:
                        stmt_sql += ","

                    need_last_call = True
                index += 1
            if need_last_call:
                con.execute(stmt_sql)

        except Exception as e:
            print(e)
        finally:
            pass

    @staticmethod
    def load_file_data(filePath=None, con=None, schema=None, table=''):
        count = 0
        if (filePath is None) or (not os.path.exists(filePath)) or (con is None):
            return count

        try:
            sql_stmt = "LOAD DATA INFILE \"{}\" IGNORE INTO TABLE {}.{} FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY ';';".format(filePath, schema, table)
            count = con.execute(sql_stmt)
        except Exception as e:
            print(e)
        finally:
            pass
        return count


    def _create_table_setup(self):

        column_names_and_types = self._get_column_names_and_types(
            self._sqlalchemy_type
        )

        pat = re.compile(r'\s+')
        column_names = [col_name for col_name, _, _ in column_names_and_types]
        if any(map(pat.search, column_names)):
            warnings.warn(_SAFE_NAMES_WARNING, stacklevel=6)

        escape = _get_valid_sqlite_name

        create_tbl_stmts = [escape(cname) + ' ' + ctype
                            for cname, ctype, _ in column_names_and_types]

        if self.keys is not None and len(self.keys):
            if not is_list_like(self.keys):
                keys = [self.keys]
            else:
                keys = self.keys
            cnames_br = ", ".join(escape(c) for c in keys)
            create_tbl_stmts.append(
                "PRIMARY KEY ({cnames_br})".format(cnames_br=cnames_br))

        create_stmts = ["CREATE TABLE " + escape(self.name) + " (\n" +
                        ',\n  '.join(create_tbl_stmts) + "\n) ENGINE=InnoDB DEFAULT CHARSET=utf8; "]

        ix_cols = [cname for cname, _, is_index in column_names_and_types
                   if is_index]
        if len(ix_cols):
            cnames = "_".join(ix_cols)
            cnames_br = ",".join(escape(c) for c in ix_cols)
            create_stmts.append(
                "CREATE INDEX " + escape("ix_" + self.name + "_" + cnames) +
                "ON " + escape(self.name) + " (" + cnames_br + ")")

        return create_stmts

    def _get_column_names_and_types(self, dtype_mapper):
        column_names_and_types = []

        for i in range(len(self.frame.columns)):
            column_names_and_types.append((text_type(self.frame.columns[i]),
                                           dtype_mapper(self.frame.iloc[:, i]), False))

        return column_names_and_types

    def _sqlalchemy_type(self, col):

        dtype = self.dtype or {}
        if col.name in dtype:
            return self.dtype[col.name]

        # Infer type of column, while ignoring missing values.
        # Needed for inserting typed data containing NULLs, GH 8778.
        col_type = lib.infer_dtype(col, skipna=True)

        from sqlalchemy.types import (BigInteger, Integer, Float,
                                      Text, Boolean,
                                      DateTime, Date, Time, TIMESTAMP)

        if col_type == 'datetime64' or col_type == 'datetime':
            # GH 9086: TIMESTAMP is the suggested type if the column contains
            # timezone information
            try:
                if col.dt.tz is not None:
                    return "TIMESTAMP"
            except AttributeError:
                # The column is actually a DatetimeIndex
                if col.tz is not None:
                    return "TIMESTAMP"
            return "DateTime"
        if col_type == 'timedelta64':
            warnings.warn("the 'timedelta' type is not supported, and will be "
                          "written as integer values (ns frequency) to the "
                          "database.", UserWarning, stacklevel=8)
            return "BigInteger"
        elif col_type == 'floating':
            if col.dtype == 'float32':
                return "Float"
            else:
                return "Float"
        elif col_type == 'integer':
            if col.dtype == 'int32':
                return "Integer"
            else:
                return "BigInteger"
        elif col_type == 'boolean':
            return "Boolean"
        elif col_type == 'date':
            return "Date"
        elif col_type == 'time':
            return "Time"
        elif col_type == 'complex':
            return "Text"

        return "Text"

